using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using NBear.Data;
using NBear.Common;

namespace NBear.Data.Oracle
{
    /// <summary>
    /// Oracle Page Splitter
    /// </summary>
    public class OraclePageSplit : PageSplit
    {
        #region Private Members

        /// <summary>
        /// Formats the inside params.
        /// </summary>
        /// <param name="sql">The sql.</param>
        /// <returns></returns>
        private static string FormatInsideParams(string where)
        {
            System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"(:[\w\d_]+)");
            return r.Replace(where, "$1Inside", int.MaxValue);
        }

        private static string ConstructAndCacheSplitableSelectStatement(string sql, string keyColumn)
        {
            string retStr;
            retStr = "Input sql statement is not legal!";

            if (sql.Contains("#_Begin#") && sql.Contains("#_End#"))
            {
                if (sql.ToLower().Substring(0, 6) == "select" && sql.ToLower().Substring(0, 10) != "select top" && keyColumn != null)
                {
                    string columnList = string.Empty;
                    string fromTable = string.Empty;
                    string whereClause = string.Empty;
                    string orderByClause = string.Empty;

                    //parse input select statement
                    string str = sql.Substring(6).TrimStart(new char[] { ' ' });

                    int fromBegin = str.ToLower().IndexOf(" from ");
                    columnList = str.Substring(0, fromBegin);
                    str = str.Substring(fromBegin).TrimStart(new char[] { ' ' });

                    if (str.ToLower().IndexOf(" where ") > 0)
                    {
                        int whereBegin = str.ToLower().IndexOf(" where ", str.IndexOf("#_End#"));
                        fromTable = str.Substring(0, whereBegin).Replace("#_Begin#", "").Replace("#_End#", "").Trim();
                        str = str.Substring(whereBegin).TrimStart(new char[] { ' ' });

                        if (str.ToLower().IndexOf(" order by ") > 0)
                        {
                            int orderBegin = str.ToLower().IndexOf(" order by ");
                            whereClause = str.Substring(0, orderBegin);
                            str = str.Substring(orderBegin).TrimStart(new char[] { ' ' });

                            orderByClause = str.TrimEnd(new char[] { ' ', ';' });
                        }
                        else
                        {
                            whereClause = str.TrimEnd(new char[] { ' ', ';' });
                        }
                    }
                    else if (str.ToLower().IndexOf(" order by ") > 0)
                    {
                        int orderBegin = str.ToLower().IndexOf(" order by ", str.IndexOf("#_End#"));
                        fromTable = str.Substring(0, orderBegin).Replace("#_Begin#", "").Replace("#_End#", "").Trim();
                        str = str.Substring(orderBegin).TrimStart(new char[] { ' ' });

                        orderByClause = str.TrimEnd(new char[] { ' ', ';' });
                    }
                    else
                    {
                        fromTable = str.Replace("#_Begin#", "").Replace("#_End#", "").TrimEnd(new char[] { ' ', ';' });
                    }

                    string overOrderBy = orderByClause;
                    if (overOrderBy.ToLower().IndexOf("order") < 0)
                    {
                        overOrderBy = String.Format("order by \"{0}\"", keyColumn.TrimStart('[').TrimEnd(']'));
                    }

                    retStr = string.Format("SELECT {0} FROM ( SELECT {0}, ROW_NUMBER() OVER ({4}) as \"DataRow_Pos\" {1} {2} ) \"tmp\" where \"tmp\".\"DataRow_Pos\" > #BeforeCount# and \"tmp\".\"DataRow_Pos\" <= #BeforeCount# + #PageSize# {3}",
                        columnList, fromTable, whereClause, orderByClause, overOrderBy).Replace("#PageSize#", "{0}").Replace("#BeforeCount#", "{1}");
                }
            }
            else
            {
                if (sql.ToLower().Substring(0, 6) == "select" && sql.ToLower().Substring(0, 10) != "select top" && keyColumn != null)
                {
                    string columnList = string.Empty;
                    string fromTable = string.Empty;
                    string whereClause = string.Empty;
                    string orderByClause = string.Empty;

                    //parse input select statement
                    string str = sql.Substring(6).TrimStart(new char[] { ' ' });

                    int fromBegin = str.ToLower().IndexOf(" from ");
                    columnList = str.Substring(0, fromBegin);
                    str = str.Substring(fromBegin).TrimStart(new char[] { ' ' });

                    if (str.ToLower().IndexOf(" where ") > 0)
                    {
                        int whereBegin = str.ToLower().IndexOf(" where ");
                        fromTable = str.Substring(0, whereBegin);
                        str = str.Substring(whereBegin).TrimStart(new char[] { ' ' });

                        if (str.ToLower().IndexOf(" order by ") > 0)
                        {
                            int orderBegin = str.ToLower().IndexOf(" order by ");
                            whereClause = str.Substring(0, orderBegin);
                            str = str.Substring(orderBegin).TrimStart(new char[] { ' ' });

                            orderByClause = str.TrimEnd(new char[] { ' ', ';' });
                        }
                        else
                        {
                            whereClause = str.TrimEnd(new char[] { ' ', ';' });
                        }
                    }
                    else
                    {
                        if (str.ToLower().IndexOf(" order by ") > 0)
                        {
                            int orderBegin = str.ToLower().IndexOf(" order by ");
                            fromTable = str.Substring(0, orderBegin);
                            str = str.Substring(orderBegin).TrimStart(new char[] { ' ' });

                            orderByClause = str.TrimEnd(new char[] { ' ', ';' });
                        }
                        else
                        {
                            fromTable = str.TrimEnd(new char[] { ' ', ';' });
                        }
                    }

                    string overOrderBy = orderByClause;
                    if (overOrderBy.ToLower().IndexOf("order") < 0)
                    {
                        overOrderBy = String.Format("order by [{0}]", keyColumn.TrimStart('[').TrimEnd(']'));
                    }

                    retStr = string.Format("SELECT {0} FROM ( SELECT {0}, ROW_NUMBER() OVER ({4}) as \"DataRow_Pos\" {1} {2} ) \"tmp\" where \"tmp\".\"DataRow_Pos\" > #BeforeCount# and \"tmp\".\"DataRow_Pos\" <= #BeforeCount# + #PageSize# {3}",
                        columnList, fromTable, whereClause, orderByClause, overOrderBy).Replace("#PageSize#", "{0}").Replace("#BeforeCount#", "{1}");
                }
            }

            _SplitableStatementCache.Add(sql, retStr);
            return retStr;
        }

        #endregion

        #region Impl PageSplit Members

        /// <summary>
        /// construct an page splitable select statement from a simple select statement like 
        /// " select [columns] from [table_name] sql [condition] order by [order_list] "
        /// </summary>
        protected override string ConstructPageSplitableSelectStatement(string sql, string keyColumn)
        {
            Check.Require(sql != null && keyColumn != null);

            string retStr;

            lock (_SplitableStatementCache)
            {
                if (_SplitableStatementCache.ContainsKey(sql))
                {
                    retStr = _SplitableStatementCache[sql];
                }
                else
                {
                    retStr = ConstructAndCacheSplitableSelectStatement(sql, keyColumn);
                }
            }

            return retStr;
        }

        /// <summary>
        /// ConstructPageSplitableSelectStatementForFirstPage
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="keyColumn">keyColumn</param>
        /// <returns></returns>
        protected override string ConstructPageSplitableSelectStatementForFirstPage(string sql, string keyColumn)
        {
            return ConstructPageSplitableSelectStatement(sql, keyColumn).Replace("{1}", "0");
        }

        /// <summary>
        /// ConstructSelectCountStatement
        /// </summary>
        /// <param name="sql">sql</param>
        /// <returns></returns>
        protected override string ConstructSelectCountStatement(string sql)
        {
            if (sql.Contains("#_Begin#") && sql.Contains("#_End#"))
            {
                string table = sql.Substring(sql.IndexOf("#_Begin#") + "#_Begin#".Length, sql.IndexOf("#_End#") - sql.IndexOf("#_Begin#") - "#_Begin#".Length).Trim();
                if (table.ToLower().Contains(" order by "))
                {
                    table = table.Substring(0, table.ToLower().IndexOf(" order by ")) + table.Substring(table.ToLower().IndexOf(" order by ") + " order by ".Length);
                }

                return string.Format("select Count(*) from {0}", table);
            }
            else
            {
                return string.Format("SELECT COUNT(*) FROM ( {0} ) \"tmp\"",
                    (sql.ToLower().IndexOf("order by") > 0 ? sql.Substring(0, sql.ToLower().IndexOf("order by")) : sql));
            }
        }

        #endregion

        #region Constructors

        public OraclePageSplit(Database db, string selectStatement, string keyColumn, params object[] paramValues)
            : base(db, selectStatement, keyColumn)
        {
            if (paramValues != null && paramValues.Length > 0)
            {
                this.paramValues = new object[paramValues.Length * 2];
                int i = 0;
                foreach (object item in paramValues)
                {
                    this.paramValues[i++] = item;
                }
                foreach (object item in paramValues)
                {
                    this.paramValues[i++] = item;
                }
            }
        }

        #endregion
    }
}
